const sql = require('mssql')

/**
 * 查询商品库存
 */
const reviewsCount = (bm, ph, ckbm, yxqz, scrq, cbjj) => {
    return sql.query `SELECT KCSL FROM KC_KCDTB WHERE BMBM = 07 AND YPBM = ${bm} AND SCPH = ${ph} AND CKBM = ${ckbm}  AND YXQZ = ${yxqz}  AND SCRQ = ${scrq} AND CBJJ = ${cbjj}`
}

/**
 * 查询订单状态
 */
const reviewsOrder = id => {
    return sql.query `SELECT SH FROM XDD_ZB WHERE ID = ${id}`
}

/**
 * 查询订单是否已经写入xsd_zb里面
 */
const isWrite = id => {
    return sql.query `SELECT ID FROM XSD_ZB WHERE ID_DHD = ${id}`
}



/**
 * 查询订单
 */
const sendOrderData = body => {

    let startSql = `SELECT * FROM (SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY Z.ID DESC)PX, Z.ID,Z.DWBM,Z.RQ,Z.DWMC, Z.JSFSBM, Z.JSFSMC ,Z.ZSL ,Z.ZJS,Z.ZSS,Z.ZSE,Z.ZHJ,Z.ZJE ,Z.YJS,Z.SH,Z.BZ FROM XDD_ZB Z WHERE (SELECT COUNT(1) AS TOTAL FROM XSD_ZB WHERE XSD_ZB.ID_DHD=Z.ID)=0 AND Z.SH = 'T'`
    const endSql = `) AS XDD LEFT JOIN (SELECT DH,BM,DZ,DWLX FROM BM_WLDW)AS BM ON BM.BM=XDD.DWBM LEFT JOIN (SELECT CASE WHEN DBJG.MZ = 0 THEN '满足' ELSE '不满足' END AS SFMZ,DBJG.ID AS JH_ID, ISNULL(DBJG.MZ, 1) AS JG_MZ FROM (SELECT SUM(DB) AS MZ,X_MX.ID FROM (SELECT MX.ID,MX.YPBM,MX.SL,CASE WHEN KC.KC_SL >= MX.SL THEN 0 ELSE 1 END AS DB FROM (SELECT YPBM,SUM(SL) AS SL,ID FROM XDD_MX GROUP BY YPBM,ID) MX LEFT JOIN (SELECT YPBM AS KC_YPBM,SUM(KCSL) AS KC_SL FROM KC_KCDTB WHERE BMBM='07' GROUP BY YPBM)AS KC ON KC.KC_YPBM=MX.YPBM)AS X_MX GROUP BY X_MX.ID)AS DBJG)AS JH  ON JH.JH_ID=XDD.ID where  XDD.RQ>='${body.T1 + ' 00:00:00'}' AND XDD.RQ<= '${body.T2 + ' 23:59:59'}') AS BC WHERE BC.PX BETWEEN ${body.NUM * 10 - 9} AND ${body.NUM * 10}`

    if (body.JBRBM) {
        startSql += `AND Z.JSFSBM = ${body.JBRBM}`
    }

    if (body.KHMC) {

        startSql += `AND Z.DWMC = '${body.KHMC}'`
    }

    startSql += endSql;
    console.log('startSql',startSql);
    return sql.query(startSql)

}

/**
 * 查询商品的详细信息
 */
const sendDetail = ({id,BMBM,DWBM,DWLX}) => {
    console.log(id);
    console.log(BMBM);
    console.log(DWBM);
    console.log(DWLX);
    // let startSql = `SELECT m.ID,m.YPBM,m.YPMC,m.YPGG,m.JLDW,m.SCCJ,m.PZWH,m.SL,m.DJ,m.JE,m.ZK,m.JXMC,m.YJ,产品.SCPH,产品.RKRQ,产品.DWBM,产品.SCRQ,产品.YXQZ,产品.KCSL,产品.CKBM,产品.CKMC,产品.CBJJ,b.MJSL,产品.HWBH,b.CCTJ,b.YXFL,b.CDMC,b.TM FROM (SELECT YPBM,SUM(SL) AS SL,ID,YPMC,YPGG,JLDW,SCCJ,PZWH,JXMC,DJ,JE,ZK,YJ FROM XDD_MX GROUP BY YPBM,ID,YPMC,YPGG,JLDW,SCCJ,PZWH,JXMC,DJ,JE,ZK,YJ) AS m , (SELECT * FROM KC_KCDTB K WHERE K.KCSL > 0) AS 产品,(SELECT * FROM BM_YP B)AS b WHERE m.YPBM = 产品.YPBM AND 产品.BMBM = '07' AND M.ID = ${id} AND b.BM = m.YPBM AND (SELECT JYFW FROM BM_BM WHERE BM = ${BMBM}) LIKE '%'+b.JYFL+'%' `;
    let startSql = `SELECT m.ID,m.YPBM,m.YPMC,m.YPGG,m.JLDW,m.SCCJ,m.PZWH,m.SL,m.DJ,m.JE,m.ZK,m.JXMC,m.YJ,产品.SCPH,产品.RKRQ,产品.DWBM,产品.SCRQ,产品.YXQZ,产品.KCSL,产品.CKBM,产品.CKMC,产品.CBJJ,b.MJSL,产品.HWBH,b.CCTJ,b.YXFL,b.CDMC,b.TM FROM (SELECT YPBM,SUM(SL) AS SL,ID,YPMC,YPGG,JLDW,SCCJ,PZWH,JXMC,DJ,JE,ZK,YJ FROM XDD_MX GROUP BY YPBM,ID,YPMC,YPGG,JLDW,SCCJ,PZWH,JXMC,DJ,JE,ZK,YJ) AS m , (SELECT * FROM KC_KCDTB K WHERE K.KCSL > 0) AS 产品,(SELECT * FROM BM_YP B)AS b WHERE m.YPBM = 产品.YPBM AND 产品.BMBM = '07' AND M.ID = ${id} AND b.BM = m.YPBM  `;
    
    // if(DWLX == 1||DWLX == 2){
    //     startSql+=` AND (SELECT JYFW FROM BM_WLDW WHERE BM = ${DWBM}) LIKE '%'+b.JYFL+'%' `;
    // }
    startSql += ` ORDER BY 产品.YXQZ ASC`;
    console.log('startSql',startSql);
    return sql.query(startSql)
}

/**
 * 获取最新一条数据的ID
 */
const sendStream = () => {
    return sql.query `SELECT TOP 1 ID FROM [dbo].[XSD_ZB] ORDER BY ID DESC`
}

/**
 * 更新订单备注
 */
const setRemark = (BZ, ID) => {
    return sql.query `UPDATE XDD_ZB SET BZ = ${BZ} WHERE ID=${ID}`
}

const getZB = (ID) => {
    return sql.query `SELECT * FROM XSD_ZB WHERE ID = ${ID}`
}


/**
 * 写入销售订单总表
 */
const updateZB = (newID, newTime, j, body, abs, zcb) => {
    return sql.query `INSERT INTO XSD_ZB (ID, RQ, DWBM, DWMC, LXDH, BMBM, BMMC, JBRBM, JBRMC, JSFSBM, JSFSMC, ZDR, SHR, ZDZK, SKQX, SKQZ, PJLX, ZSL, ZJS, ZSS, ZJE, ZSE, ZHJ, YJS, XJS, EJS, WJS, ZZK, ZCB, ZML, YHE, DXJF, BCJF, CZJE, SK, ZL, LX, FS, SH, ID_DHD)
                SELECT ${newID},${newTime},${j.DWBM},${j.DWMC}, ${j.DH},${
              body.value.BMBM
            }, ${body.value.BMMC},${body.value.BMBM + body.value.JBRBM},${
              body.value.JBRMC
            },${j.JSFSBM},${j.JSFSMC},${body.value.ZDR},${
              body.value.SHR
            },${100},${360},${abs},${'0'} ,${j.ZSL},${j.ZJS} ,${j.ZSL} ,${
              j.ZJE
            } , ${j.ZSE},${j.ZHJ} , ${j.YJS}, ${j.ZJE},${j.ZJE}, ${j.ZHJ -
              j.ZJE},${0},${zcb},${j.ZJE - zcb},${0},${0},${parseInt(
              j.ZJE / 10
            )},${0},${j.ZJE},${0},${body.value.LXMC},${1},${body.value.SH},${
              j.ID
            }
                 `
}

/**
 * 写入销售订单JS表
 */
const updateJS = (newID, j, body) => {
    return sql.query `INSERT INTO XSD_JS (ID,XH,BM,MC,SK,ZL,JSJE,LX) SELECT ${newID}, ${1},${
              j.JSFSBM
            },${j.JSFSMC},${j.ZJE},${j.ZL},${j.ZJE},${body.value.LX}`
}

/**
 * 更新商品库存
 */
const updateKC = (CXSL, we) => {
    return sql.query `UPDATE KC_KCDTB SET KCSL = ${CXSL -
            we.SL},KYSL =${CXSL - we.SL},KCJE=${(CXSL - we.SL) *
            we.CBJJ} WHERE BMBM = 07 AND YPBM = ${we.YPBM} AND SCPH = ${
            we.SCPH
          } AND CKBM = ${we.CKBM}  AND YXQZ = ${we.YXQZ}  AND SCRQ = ${
            we.SCRQ
          } AND CBJJ = ${we.CBJJ} AND RKRQ = ${we.RKRQ} AND DWBM = ${we.DWBM}`

}

/**
 * 写入销售订单MX表
 */
const updateMX = (we, newID, shijian, nb, day, abs) => {
    console.log('WE',we)
    console.log('WECBJJ',we.CBJJ)
    console.log('updatemx',`INSERT INTO XSD_MX (ID,XH,NB,YPBM,YPMC,YPGG,JXMC,SCCJ,CDMC,PZWH,SCPH,SCRQ,YXQX,JXQX,YXQZ,JLDW,MJSL,XS,SL,JS,SS,YJ,YJ_HS,ZK,DJ,DJ_HS,DJ_LSJ,DJ_ZDJ,JE,RT,SE,HJ,CBJJ,XSCB,XSML,CKBM,CKMC,ZP,DZ,JF,CB,FHRQ,BZQK,ZLZK,FHJL,HWBH) SELECT ${newID}, ${shijian},${parseInt(
        nb
      )},${we.YPBM},${we.YPMC},${we.YPGG},${we.JXMC},${we.SCCJ},${
        we.CDMC
      },${we.PZWH},${we.SCPH},${we.SCRQ},${day},${30},${we.YXQZ},${
        we.JLDW
      },${0},${we.SL},${we.SL},${0},${we.SL},${we.DJ},${we.DJ},${we.ZK},${
        we.DJ
      },${we.DJ},${we.DJ},${0},${we.JE},${0},${0},${we.HJ},${we.CBJJ},${
        we.CBJJ * we.SL
      },${we.XSML},${we.CKBM},${
        we.CKMC
      },${'F'},${'T'},${'T'},${'A'},${abs},${'合格'},${'合格'},${'合格'},${we.HWBH}`);
    return sql.query `INSERT INTO XSD_MX (ID,XH,NB,YPBM,YPMC,YPGG,JXMC,SCCJ,CDMC,PZWH,SCPH,SCRQ,YXQX,JXQX,YXQZ,JLDW,MJSL,XS,SL,JS,SS,YJ,YJ_HS,ZK,DJ,DJ_HS,DJ_LSJ,DJ_ZDJ,JE,RT,SE,HJ,CBJJ,XSCB,XSML,CKBM,CKMC,ZP,DZ,JF,CB,FHRQ,BZQK,ZLZK,FHJL,HWBH) SELECT ${newID}, ${shijian},${parseInt(
            nb
          )},${we.YPBM},${we.YPMC},${we.YPGG},${we.JXMC},${we.SCCJ},${
            we.CDMC
          },${we.PZWH},${we.SCPH},${we.SCRQ},${day},${30},${we.YXQZ},${
            we.JLDW
          },${0},${we.SL},${we.SL},${0},${we.SL},${we.DJ},${we.DJ},${we.ZK},${
            we.DJ
          },${we.DJ},${we.DJ},${0},${we.JE},${0},${0},${we.HJ},${we.CBJJ},${
            we.CBJJ * we.SL
          },${we.XSML},${we.CKBM},${
            we.CKMC
          },${'F'},${'T'},${'T'},${'A'},${abs},${'合格'},${'合格'},${'合格'},${we.HWBH}`
}

module.exports = {
    reviewsCount,
    reviewsOrder,
    sendOrderData,
    sendDetail,
    sendStream,
    setRemark,
    isWrite,
    updateZB,
    updateJS,
    updateKC,
    updateMX,
    getZB
}
